package cc.idoone.spring_boot_hello.h2;

import cc.idoone.spring_boot_hello.h2.jpa.StaffBo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;

/**
 * JdbcTemplate
 *
 * @auther hemj
 * @since 2017/9/23
 */
@Repository
public class JdbcStaffDao {

	@Autowired
	@Qualifier("h2JdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	@Autowired
	@Qualifier("h2NamedJdbcTemplate")
	private NamedParameterJdbcTemplate namedJdbcTemplate;

	public int createTable() {
		try {
			DatabaseMetaData meta = jdbcTemplate.getDataSource().getConnection().getMetaData();
			String type [] = {"TABLE"};
			ResultSet rs = meta.getTables(null, null, "staff", type);
			if (rs.next()) {
				return 1;
			} else {
				System.out.println("表格不存在");
				return jdbcTemplate.update("CREATE TABLE IF NOT EXISTS staff(\n" +
						"  id char(20) not null primary key,\n" +
						"  name char(20),\n" +
						"  age INTEGER\n" +
						")");
			}
		} catch (SQLException ignored) {
			return 0;
		}
	}

	public StaffBo save(final StaffBo staff) {
		final String sql = "insert into staff(id,name,age) values(?,?,?)";

		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, staff.getUserId());
				ps.setString(2, staff.getUserName());
				ps.setInt(3, staff.getAge());
				return ps;
			}
		});

		return staff;
	}

	public Iterable<StaffBo> findAll() {
		return jdbcTemplate.query("select id, name, age from staff", new StaffRowMapper());
	}

	public StaffBo findByUserName(String userName) {
		List<StaffBo> list = jdbcTemplate.query("select id, name, age from staff where name=?",
				new Object[]{userName}, new StaffRowMapper());
		if (list.size() == 1) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public List<StaffBo> findByUserNameIgnoreCase(String name) {
		name = "%" + name + "%";
		return jdbcTemplate.query("select id, name, age from staff where name like ?",
				new Object[]{name}, new StaffRowMapper());
	}

	public List<StaffBo> findByUserIdAndUserNameAllIgnoreCase(String id, String name) {
		id = "%" + id + "%";
		name = "%" + name + "%";
		return jdbcTemplate.query("select id, name, age from staff where id like ? and name like ?",
				new Object[]{id, name}, new StaffRowMapper());
	}

	public List<StaffBo> getByMinAge(int minAge) {
		HashMap param = new HashMap<>();
		param.put("age", minAge);
		return namedJdbcTemplate.query("select id, name, age from staff c where c.age > :age",
				param, new StaffRowMapper());
	}

	public StaffBo findByUserId(String id) {
		HashMap param = new HashMap<>();
		param.put("id", id);
		List<StaffBo> list = namedJdbcTemplate.query("select id, name, age from staff c where c.id = :id",
				param, new StaffRowMapper());
		if (list.size() == 1) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public Page<StaffBo> findByAgeGreaterThan(Integer age, Pageable pageable) {
		return null;
	}

	public int increaseAge(int after, int before) {
		String sql = "update staff a set a.age = :after where a.age < :before";
		HashMap param = new HashMap();
		param.put("after", after);
		param.put("before", before);
		return namedJdbcTemplate.update(sql, param);
	}
}

class StaffRowMapper implements RowMapper<StaffBo> {

	@Override
	public StaffBo mapRow(ResultSet rs, int rowNum) throws SQLException {
		StaffBo staff = new StaffBo();
		staff.setUserId(rs.getString("id"));
		staff.setUserName(rs.getString("name"));
		staff.setAge(rs.getInt("age"));

		return staff;
	}
}
